/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package kindergarden.at.google;
import java.io.Serializable;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.sql.*;
import java.util.ArrayList;
import java.util.TreeMap;
/**
 *
 * @author rogu0903
 */
public class SQLHandler implements Serializable
{
   private  ResultSet rs;
   private  Statement s;

    public SQLHandler()
    {}


public void insertChild(Child ch){
   try
   {
        s = DatabaseConnection.connection.createStatement ();
        s.executeUpdate (
               "INSERT INTO child (home_phone_number, home_phone_number2, attendence, attendence_time, name, sin_num, comment)"
               + " VALUES"
               + "('"+ch.getHome_phone_number()+"', '"+ch.getHome_phone_number2()+"', '"+ch.isAttendence()+
               "', '"+ch.getAttendence_time()+"', '"+ch.getName()+"', '"+ch.getSin_num()+"', '"+ch.getComment()+"')"

               );
        
        s.close ();
          s = DatabaseConnection.connection.createStatement ();
        s.executeQuery ("SELECT * FROM version");
        rs = s.getResultSet ();
        rs.next ();
              System.out.println("Senast updaterad:"+rs.getLong("lastupdate"));
              long num=rs.getLong("lastupdate")+1;
              
           s.executeUpdate (
                   "UPDATE version SET lastupdate='"+num+"' WHERE id = '1'");
       System.out.println ("Funkade skit bra");
        s.close ();
        rs.close ();
  
    }
       catch (Exception ex)
        {
            Logger lgr = Logger.getLogger(SQLHandler.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);

        }
  // System.out.println (count + " rows were inserted")
}

public void insertCaretaker(Caretaker ca){
   try
      {
        s = DatabaseConnection.connection.createStatement ();
        s.executeUpdate (
               "INSERT INTO caretaker (name, email, mobile_num, phone_number_work, workplace)"
               + " VALUES"
               + "('"+ca.getName()+"', '"+ ca.getEmail()+"', '"+ ca.getMobile_num()+"', '"+ ca.getPhone_number_work()+"', '"+ ca.getWorkplace()+"')"

               );
        s.close ();
    }
       catch (Exception ex)
        {
            Logger lgr = Logger.getLogger(SQLHandler.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);

        }
  // System.out.println (count + " rows were inserted")
}
 public TreeMap getChildList(){
     TreeMap  tm = new TreeMap();

     try{
        s = DatabaseConnection.connection.createStatement ();
        s.executeQuery ("SELECT home_phone_number, home_phone_number2, attendence, attendence_time, name, sin_num, comment, id FROM child");
        rs = s.getResultSet ();

        int count = 0;
        Child listChild;
        while (rs.next ())
        {
            listChild= new Child(rs.getString("home_phone_number"),rs.getString ("home_phone_number2"),rs.getShort("attendence"),
                    rs.getInt ("attendence_time"), rs.getString ("name"),rs.getString ("sin_num"), rs.getString ("comment"), rs.getInt("id") );
            tm.put(listChild.getSin_num(), listChild.childToString());
            ++count;

        }

        rs.close ();
        s.close ();
        System.out.println (count + " rows were retrieved");

     }
        catch (Exception ex)
        {
            Logger lgr = Logger.getLogger(SQLHandler.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);

        }

     return tm;
 }
 public ArrayList<Child> getChildList2(){
     ArrayList <Child> al= new ArrayList<Child>();

     try{
        s = DatabaseConnection.connection.createStatement ();
        s.executeQuery ("SELECT home_phone_number, home_phone_number2, attendence, attendence_time, name, sin_num, comment, id FROM child");
        rs = s.getResultSet ();

        int count = 0;
        Child listChild;
        while (rs.next ())
        {
            listChild= new Child(rs.getString("home_phone_number"),rs.getString ("home_phone_number2"),rs.getShort("attendence"),
                    rs.getInt ("attendence_time"), rs.getString ("name"),rs.getString ("sin_num"), rs.getString ("comment"), rs.getInt("id") );
           // al.add(listChild.getSin_num(), listChild.childToString());
            al.add(listChild);
            ++count;

        }

        rs.close ();
        s.close ();
        System.out.println (count + " rows were retrieved");

     }
        catch (Exception ex)
        {
            Logger lgr = Logger.getLogger(SQLHandler.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);

        }

     return al;
 }


  public Child getChild(int pSin_num){
     Child child = new Child();
      try{

        s = DatabaseConnection.connection.createStatement ();
        s.executeQuery ("SELECT home_phone_number, home_phone_number2, attendence, attendence_time, name, sin_num, comment FROM child WHERE sin_num ="+pSin_num+"") ;
        rs = s.getResultSet ();
        int count = 0;
        rs.next ();
        child= new Child(rs.getString ("home_phone_number"),rs.getString ("home_phone_number2"),rs.getShort("attendence"),
                    rs.getInt ("attendence_time"), rs.getString ("name"),rs.getString ("sin_num"), rs.getString ("comment"), rs.getInt("id") );

            ++count;

        rs.close ();
        s.close ();
        System.out.println (count + " rows were retrieved");

     }
        catch (Exception ex)
        {
            Logger lgr = Logger.getLogger(SQLHandler.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);

        }
     return child;
 }


}
